package com.haiqiu.tools.excel.utils;


import com.haiqiu.tools.common.utils.DateEnum;
import com.haiqiu.tools.common.utils.DateUtils;
import com.haiqiu.tools.common.utils.HttpServletUtils;
import com.haiqiu.tools.common.utils.StringUtils;
import com.haiqiu.tools.excel.annotation.Excel;
import com.haiqiu.tools.excel.style.StyleUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.lang.NonNull;
import org.springframework.lang.Nullable;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.*;

import static com.haiqiu.tools.common.utils.DateUtils.DATE_TIME_PATTERN;


/**
 * 类用途:excel导入工具类
 *
 * @author haiqiu
 */
public class ExcelUtils {
    private static final String XLS = "^.+\\.(?i)(xls)$";
    private static final String XLSX = "^.+\\.(?i)(xlsx)$";

    /**
     * 将list数据源的数据导入到excel表单,并返回输出流
     *
     * @param list      数据源
     * @param sheetName 工作表的名称
     */
    public static <T> void getListToExcel(@NonNull Class<T> clazz, @NonNull List<T> list, @NonNull String sheetName) {
        getListToExcel(clazz, list, sheetName, null);
    }


    /**
     * 将list数据源的数据导入到excel表单,并返回输出流
     *
     * @param list      数据源
     * @param sheetName 工作表的名称
     * @param title     表格标题
     */
    public static <T> void getListToExcel(@NonNull Class<T> clazz, @NonNull List<T> list,
                                          @NonNull String sheetName, String title) {

        SXSSFWorkbook workbook = getWorkbook(clazz, list, sheetName, title);
        //导出
        export(sheetName, workbook, HttpServletUtils.getServletResponse());
    }


    /**
     * 将list数据源的数据导入到excel表单,并返回InputStream
     *
     * @param list      数据源
     * @param sheetName 工作表的名称
     * @param title     表格标题
     */
    public static <T> InputStream getListToInputStream(@NonNull Class<T> clazz, @NonNull List<T> list,
                                          @NonNull String sheetName, String title) {

        SXSSFWorkbook workbook = getWorkbook(clazz, list, sheetName, title);
        //导出
        return workbookConvertorStream(workbook);
    }

    /**
     * 将数据导出到Excel文件到固定路径文件下
     *
     * @param clazz      类
     * @param list       数据集合
     * @param sheetName  sheetName
     * @param exportPath 导出路径
     * @param title      表格标题
     * @param <T>        范型
     */
    public static <T> void getListToExcelFile(@NonNull Class<T> clazz, @NonNull List<T> list,
                                              @NonNull String sheetName, String title, @NonNull String exportPath) {
        SXSSFWorkbook workbook = getWorkbook(clazz, list, sheetName, title);
        //输出到文件
        try {
            String path = exportPath + "/" + sheetName + DateUtils.format(new Date(), DateEnum.CURRENCY.getTypeName()) + ".xlsx";
            FileOutputStream outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将数据导出到Excel文件到固定路径文件下
     *
     * @param clazz      类
     * @param list       数据集合
     * @param sheetName  sheetName
     * @param exportPath 导出路径
     * @param <T>        范型
     */
    public static <T> void getListToExcelFile(@NonNull Class<T> clazz, @NonNull List<T> list,
                                              @NonNull String sheetName, @NonNull String exportPath) {
        getListToExcelFile(clazz, list, sheetName, null, exportPath);
    }

    /**
     * 构造工作博数据
     *
     * @param clazz     类
     * @param list      数据
     * @param sheetName sheetName
     * @param <T>       范型
     * @param title     表格标题
     * @return 工作博
     */
    private static <T> SXSSFWorkbook getWorkbook(Class<T> clazz, List<T> list, String sheetName, String title) {
        if (list.size() == 0) {
            throw new RuntimeException("数据为空");
        }
        // 得到所有定义字段
        List<Field> fields = getAllFields(clazz);
        // 产生工作薄对象
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        try {
            createSheet(list, sheetName, fields, workbook, title);
        } catch (Exception e) {
            throw new RuntimeException("将list数据源的数据导入到excel表单时异常!", e);
        }
        return workbook;
    }


    /**
     * 获取反射过后的数据
     *
     * @param clazz 泛型数据
     * @return 成员列表
     */
    private static List<Field> getFields(Field[] clazz) {
        List<Field> fields = new ArrayList<>();
        // 得到所有field并存放到一个list中
        for (Field field : clazz) {
            if (field.isAnnotationPresent(Excel.class)) {
                fields.add(field);
            }
        }
        return fields;
    }

    /**
     * 获取反射后的注解字段
     * @param clazz 类
     * @return 字段
     */
    public static <T> List<Field> getAllFields(Class<T> clazz){
        List<Field> fieldList = new ArrayList<>();
        while (clazz != null){
            List<Field> fields = getFields(clazz.getDeclaredFields());
            fieldList.addAll(fields);
            clazz = (Class<T>) clazz.getSuperclass();
        }
        return fieldList;
    }

    /**
     * 根据 List<Map<String, Object>> maps 数据格式进行导出
     *
     * @param maps      List<Map<String, Object>> maps
     * @param sheetName sheetName
     */
    public static void getMapsToExcel(@NonNull List<Map<String, Object>> maps, @NonNull String sheetName) {
        getMapsToExcel(maps, sheetName, null);
    }

    /**
     * 根据 List<Map<String, Object>> maps 数据格式进行导出
     *
     * @param maps      List<Map<String, Object>> maps
     * @param sheetName sheetName
     * @param title     表格标题
     */
    public static void getMapsToExcel(@NonNull List<Map<String, Object>> maps, @NonNull String sheetName, String title) {

        if (maps.size() == 0) {
            throw new RuntimeException("数据为空");
        }
        // 产生工作薄对象
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        try {
            createSheet(maps, sheetName, workbook, title);
        } catch (Exception e) {
            throw new RuntimeException("将list数据源的数据导入到excel表单时异常!", e);
        }
        export(sheetName, workbook, HttpServletUtils.getServletResponse());
    }

    /**
     * 根据 List<Map<String, Object>> maps 数据格式进行导出到文件
     *
     * @param maps      List<Map<String, Object>> maps
     * @param sheetName sheetName
     */
    public static void getMapsToExcelFile(@NonNull List<Map<String, Object>> maps, @NonNull String sheetName,
                                          @NonNull String exportPath) {

        getMapsToExcelFile(maps, sheetName, null, exportPath);
    }

    /**
     * 根据 List<Map<String, Object>> maps 数据格式进行导出到文件
     *
     * @param maps      List<Map<String, Object>> maps
     * @param sheetName sheetName
     * @param title     表格标题
     */
    public static void getMapsToExcelFile(@NonNull List<Map<String, Object>> maps, @NonNull String sheetName,
                                          String title, @NonNull String exportPath) {

        if (maps.size() == 0) {
            throw new RuntimeException("数据为空");
        }
        // 产生工作薄对象
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        try {
            createSheet(maps, sheetName, workbook, title);
        } catch (Exception e) {
            throw new RuntimeException("将list数据源的数据导入到excel表单时异常!", e);
        }
        //输出到文件
        try {
            String path = exportPath + "/" + sheetName + DateUtils.format(new Date(), DateEnum.CURRENCY.getTypeName()) + ".xlsx";
            FileOutputStream outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 构造数据结构
     *
     * @param maps      List<Map<String, Object>> maps 数据格式
     * @param sheetName sheetName
     * @param workbook  workbook
     * @param title     表格标题
     */
    private static void createSheet(List<Map<String, Object>> maps, String sheetName, SXSSFWorkbook workbook, String title) {
        // 产生工作表对象
        SXSSFSheet sheet = workbook.createSheet();
        //缩放比列
        sheet.setZoom(110);
        //自动调整列的大小。
        sheet.trackColumnForAutoSizing(0);
        sheet.trackColumnForAutoSizing(1);
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        // 设置工作表的名称.
        workbook.setSheetName(0, sheetName);
        SXSSFRow row;
        SXSSFCell cell;
        // 产生单元格
        Map<String, Object> map = new HashMap<>();
        if (StringUtils.hasText(title)) {
            SXSSFRow rowHeader = sheet.createRow(0);
            map = StyleUtils.getHeaderToMap(maps, title, workbook, sheet, rowHeader);
        }

        //设置标题列
        row = sheet.createRow(StringUtils.hasText(title) ? 1 : 0);

        // 普通列样式
        Font font = workbook.createFont();
        CellStyle cellStyle = workbook.createCellStyle();
        font.setFontName("黑体");


        //设置时间格式转换
        CreationHelper createHelper = workbook.getCreationHelper();
        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(DATE_TIME_PATTERN));
        dateStyle.setFont(font);

        /* *************创建列头名称*************** */
        int col = 0;
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            // 创建列
            cell = row.createCell(col);
            //设置表头样式
            CellStyle titleStyle = StyleUtils.getTitleStyle(workbook);
            cell.setCellStyle(titleStyle);
            sheet.setColumnWidth(col, ((entry.getKey().length()) * 8 * 256));
            // 设置列中写入内容为String类型
            cell.setCellType(CellType.STRING);
            // 写入列名
            cell.setCellValue(entry.getKey());
            //设置批注
            Comment commentByContent = StyleUtils.getComment(workbook, sheet, row, cell, cell.getStringCellValue());
            // 将注释分配给单元格
            cell.setCellComment(commentByContent);
            //设置宽度
            autoWidth(sheet, entry.getKey(), col);
            col++;
        }
        /* *************创建内容列*************** */
        int startNo = row.getCell(0).getCellComment() != null ? row.getRowNum() + 1 : 1;
        // 写入各条记录,每条记录对应excel表中的一行
        for (Map<String, Object> stringObjectMap : maps) {
            row = sheet.createRow(startNo);
            startNo++;
            // 得到导出对象
            int cellCount = 0;
            for (Map.Entry<String, Object> entry : stringObjectMap.entrySet()) {
                cell = row.createCell(cellCount);
                cell.setCellStyle(cellStyle);
                // 如果数据存在就填入,不存在填入空格
                if (entry != null) {
                    setCellValue(cell, entry.getValue());
                } else {
                    cell.setCellValue("");
                }
                assert entry != null;
                if (Date.class.equals(entry.getValue().getClass())) {
                    cell.setCellStyle(dateStyle);
                }
                cellCount++;
            }

        }
    }



    public static <T> List<T> getExcelToList(@Nullable String sheetName, String title, @NonNull MultipartFile file, @NonNull Class<T> clazz) {
        if (file.isEmpty()) {
            throw new RuntimeException("上传文件为空");
        }
        String fileName = file.getOriginalFilename();
        if (checkExcel(fileName)) {
            throw new RuntimeException("请上传正确的Excel格式");
        }
        List<T> list = new ArrayList<>();
        Workbook book = null;
        try (InputStream input = file.getInputStream()) {
            if (fileName.matches(XLS)) {
                book = new HSSFWorkbook(input);
            } else {
                book = new XSSFWorkbook(input);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        Sheet sheet = null;
        // 如果指定sheet名,则取指定sheet中的内容.
        if (StringUtils.hasText(sheetName)) {
            assert book != null;
            sheet = book.getSheet(sheetName);
        }
        // 如果传入的sheet名不存在则默认指向第1个sheet.
        if (sheet == null) {
            assert book != null;
            sheet = book.getSheetAt(0);
        }
        // 得到数据的行数
        int rows = sheet.getLastRowNum();
        // 有数据时才处理
        if (rows > 0) {
            // 得到类的所有field
            Field[] allFields = clazz.getDeclaredFields();
            // 定义一个map用于存放列的序号和field
            Map<Integer, Field> fieldsMap = new HashMap<>(32);
            for (int i = 0, index = 0; i < allFields.length; i++) {
                Field field = allFields[i];
                // 将有注解的field存放到map中
                if (field.isAnnotationPresent(Excel.class)) {
                    // 设置类的私有字段属性可访问
                    field.setAccessible(true);
                    fieldsMap.put(index, field);
                    index++;
                }
            }
            //TODO 获取批注信息，保证模板正确
            CheckComment(sheet, title);
            // 从第2行开始取数据,默认第一行是表头
            for (int i = 0; i <= rows; i++) {
                // 得到一行中的所有单元格对象.
                Row row = sheet.getRow(i);
                if (row == null) {
                    break;
                }
                T entity = null;
                //列数
                int num = sheet.getRow(i).getLastCellNum();
                int index = 0;
                for (int a = 0; a < num; a++) {
                    Cell next = row.getCell(a);
                    if (next == null) {
                        index++;
                        continue;
                    }
                    if (next.getCellComment() != null) {
                        index++;
                        continue;
                    }
                    //替换需要配置的数据列字典值
                    Excel attr = fieldsMap.get(index).getAnnotation(Excel.class);
                    String replaceVal = null;
                    if (attr.replace().length > 0) {
                        for (String data : attr.replace()) {
                            String prefix = data.substring(0, data.lastIndexOf("_"));
                            String suffix = data.substring(data.lastIndexOf("_") + 1);
                            if (suffix.equals(next.toString())) {
                                replaceVal = prefix;
                                break;
                            }
                        }
                    }
                    //格式化单元格格式
                    DataFormatter formatter = new DataFormatter();
                    String c = replaceVal == null ? formatter.formatCellValue(next) : replaceVal;
                    if (!StringUtils.hasText(c)) {
                        index++;
                        continue;
                    }
                    if (c.contains("合计：")) {
                        continue;
                    }
                    // 如果不存在实例则新建
                    try {
                        entity = entity == null ? clazz.getDeclaredConstructor().newInstance() : entity;
                    } catch (InstantiationException | NoSuchMethodException | InvocationTargetException |
                             IllegalAccessException e) {
                        throw new RuntimeException(e);
                    }
                    // 从map中得到对应列的field
                    Field field = fieldsMap.get(index);
                    if (field == null) {
                        continue;
                    }
                    // 取得类型,并根据对象类型设置值.
                    try {
                        if (!setValue(entity, c, field)) {
                            continue;
                        }
                    } catch (IllegalAccessException | ParseException e) {
                        throw new RuntimeException(e);
                    }
                    index++;
                }
                if (entity != null) {
                    list.add(entity);
                }
            }
        }
        return list;
    }

    /**
     * 将excel表单数据源的数据导入到list
     *
     * @param sheetName 工作表的名称
     * @param file      输入
     * @param clazz     要转换的对象类型
     */
    @NonNull
    public static <T> List<T> getExcelToList(@Nullable String sheetName, @NonNull MultipartFile file, @NonNull Class<T> clazz) {
        return getExcelToList(sheetName, null, file, clazz);
    }

    /**
     * 模版注释检测
     *
     * @param sheet sheet
     */
    private static void CheckComment(Sheet sheet, String title) {
        if (sheet.getRow(0) == null || sheet.getRow(0).getCell(0) == null || sheet.getRow(0).getCell(0).getCellComment() == null
                || sheet.getRow(0).getCell(0).getCellComment().getString().toString() == null) {
            throw new RuntimeException("缺少批注信息，可能是模板不正确");
        }
        if (!StringUtils.hasText(title)) {
            return;
        }
        Comment comment = sheet.getRow(0).getCell(0).getCellComment();
        String s = comment.getString().toString();
        if (!s.equals(sheet.getSheetName())) {
            throw new RuntimeException("模板错误，请选择正确的模板数据");
        }
    }

    /**
     * 将excel表单数据源的数据导入到Map
     *
     * @param sheetName 工作表的名称
     * @param file      输入
     */
    public static List<Map<String, Object>> getExcelToMaps(@Nullable String sheetName, @NonNull MultipartFile file) {
        return getExcelToMaps(sheetName, null, file);
    }

    /**
     * 将excel表单数据源的数据导入到Map
     *
     * @param sheetName 工作表的名称
     * @param file      输入
     */
    @NonNull
    public static List<Map<String, Object>> getExcelToMaps(@Nullable String sheetName, String title, @NonNull MultipartFile file) {
        if (file.isEmpty()) {
            throw new RuntimeException("上传文件为空");
        }
        String fileName = file.getOriginalFilename();
        if (checkExcel(fileName)) {
            throw new RuntimeException("请上传正确的Excel格式");
        }
        //定义Maps获取数据
        List<Map<String, Object>> maps = new ArrayList<>();

        Workbook book = null;
        try (InputStream input = file.getInputStream()) {
            if (fileName.matches(XLS)) {
                book = new HSSFWorkbook(input);
            } else {
                book = new XSSFWorkbook(input);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        Sheet sheet = null;
        // 如果指定sheet名,则取指定sheet中的内容.
        if (StringUtils.hasText(sheetName)) {
            assert book != null;
            sheet = book.getSheet(sheetName);
        }
        // 如果传入的sheet名不存在则默认指向第1个sheet.
        if (sheet == null) {
            assert book != null;
            sheet = book.getSheetAt(0);
        }
        // 得到数据的行数
        int rows = sheet.getLastRowNum();
        // 有数据时才处理
        if (rows > 0) {
            //TODO 获取批注信息，保证模板正确
            CheckComment(sheet, title);

            // 开始取数据,默认第一行是表头
            for (int i = 0; i <= rows; i++) {
                // 得到一行中的所有单元格对象.
                Row row = sheet.getRow(i);
                if (row == null) {
                    break;
                }
                //列数
                int num = sheet.getRow(i).getLastCellNum();
                int index = 0;
                //定义装数据
                Map<String, Object> map = new HashMap<>();
                for (int a = 0; a < num; a++) {
                    Cell next = row.getCell(a);
                    if (next == null) {
                        index++;
                        continue;
                    }
                    if (next.getCellComment() != null) {
                        index++;
                        continue;
                    }
                    //格式化单元格格式
                    DataFormatter formatter = new DataFormatter();
                    String c = formatter.formatCellValue(next);
                    if (!StringUtils.hasText(c)) {
                        index++;
                        continue;
                    }
                    // 取得类型,并根据对象类型设置值.
                    map.put(sheet.getRow(1).getCell(index).getStringCellValue(), c);
                    index++;
                    maps.add(map);
                }
            }
        }
        return maps;
    }

    /**
     * 生成导入模版
     *
     * @param templateName 模版名称
     * @param sheetName    sheetName
     * @param clazz        模版类
     */
    public static <T> void createTemplate(@NonNull String templateName, String sheetName, @NonNull Class<T> clazz) {
        createTemplate(templateName, sheetName, null, clazz, null);
    }

    /**
     * 生成导入模版(带示例数据)
     *
     * @param templateName 模版名称
     * @param sheetName    sheetName
     * @param clazz        模版类
     * @param list         模板数据
     * @param title        表格名称
     * @throws RuntimeException 生成模版出现意外错误
     */
    public static <T> void createTemplate(@NonNull String templateName, String sheetName,
                                          String title, @NonNull Class<T> clazz, List<T> list) {
        try {
            //反射获取类成员变量
            List<Field> fieldList = getAllFields(clazz);
            //创建工作表
            SXSSFWorkbook wb = new SXSSFWorkbook();
            // 生成数据
            createSheet(list, sheetName, fieldList, wb, title);
            //生成xlsx返回文件流
            export(templateName, wb, HttpServletUtils.getServletResponse());
        } catch (Exception e) {
            throw new RuntimeException("生成模版出现意外错误");
        }

    }

    /**
     * 生成导入模版(带示例数据)
     *
     * @param templateName 模版名称
     * @param sheetName    sheetName
     * @param clazz        模版类
     * @param list         模板数据
     * @param title        表格名称
     * @throws RuntimeException 生成模版出现意外错误
     */
    public static <T> InputStream createTemplateToInputStream(@NonNull String templateName, String sheetName,
                                          String title, @NonNull Class<T> clazz, List<T> list) {
        try {
            //反射获取类成员变量
            List<Field> fieldList = getAllFields(clazz);
            //创建工作表
            SXSSFWorkbook wb = new SXSSFWorkbook();
            // 生成数据
            createSheet(list, sheetName, fieldList, wb, title);
            //生成xlsx返回InputStream
            return workbookConvertorStream(wb);
        } catch (Exception e) {
            throw new RuntimeException("生成模版出现意外错误");
        }

    }




    /**
     * 判断是否是excel
     *
     * @param fileName 文件名
     * @return 获取结果
     */
    private static boolean checkExcel(String fileName) {
        return fileName == null || (!fileName.matches(XLS) && !fileName.matches(XLSX));
    }

    /**
     * 设置值配置
     *
     * @param entity 当前实体
     * @param c      excel当前取值
     * @param field  实体当前的域对象
     * @return 是否成功
     */
    private static <T> boolean setValue(T entity, String c, Field field) throws IllegalAccessException, ParseException {
        Class<?> fieldType = field.getType();
        if (String.class == fieldType) {
            field.set(entity, String.valueOf(c));
        } else if (BigDecimal.class == fieldType) {
            c = c.contains("%") ? c.replace("%", "") : c;
            field.set(entity, new BigDecimal(c));
        } else if (Date.class == fieldType) {
            field.set(entity, DateUtils.parseDate(c, field.getAnnotation(Excel.class).dateFormat()));
        } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
            field.set(entity, Integer.parseInt(c));
        } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
            field.set(entity, Long.valueOf(c));
        } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
            field.set(entity, Float.valueOf(c));
        } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
            field.set(entity, Short.valueOf(c));
        } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
            field.set(entity, Double.valueOf(c));
        } else if ((Boolean.TYPE == fieldType) || (Boolean.class == fieldType)) {
            field.set(entity, Boolean.valueOf(c));
        } else if (Character.TYPE == fieldType) {
            if ((c != null) && (c.length() > 0)) {
                field.set(entity, c.charAt(0));
            }
        }
        return true;
    }

    /**
     * 分别产生单个sheet
     *
     * @param list      原数据
     * @param sheetName sheet基础名
     * @param fields    实体的域对象集合
     * @param workbook  工作对象
     * @param title     表格标题
     */
    private static <T> void createSheet(List<T> list, String sheetName, List<Field> fields, SXSSFWorkbook workbook, String title) throws IllegalAccessException {
        // 产生工作表对象
        SXSSFSheet sheet = workbook.createSheet();
        //缩放比列
        sheet.setZoom(110);
        // 设置工作表的名称.
        workbook.setSheetName(0, sheetName);
        SXSSFRow row;
        SXSSFCell cell;

        // 产生单元格--->sheet.createRow(0);
        if (StringUtils.hasText(title)) {
            // 设置标题样式
            SXSSFRow rowHeader = sheet.createRow(0);
            getHeaderToClass(title, fields, workbook, sheet, rowHeader);
        }

        //设置标题列,如果创建标题就下一行创建列表名称，否则就不创建标题列
        row = sheet.createRow(StringUtils.hasText(title) ? 1 : 0);

        //普通列样式
        Font font = workbook.createFont();
        CellStyle cellStyle;
        font.setFontName("Calibri");

        //标红列样式
        Font newFont = workbook.createFont();
        CellStyle newCellStyle = workbook.createCellStyle();
        newFont.setFontName("Calibri");

        //设置时间格式转换
        DataFormat dataFormat = workbook.createDataFormat();
        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(dataFormat.getFormat(DATE_TIME_PATTERN));
        dateStyle.setFont(font);

        /* *************创建列头名称*************** */
        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            Excel attr = field.getAnnotation(Excel.class);
            int col = i;
            // 根据指定的顺序获得列号
            if (StringUtils.hasText(attr.column())) {
                col = getExcelCol(attr.column());
            }
            // 创建列
            cell = row.createCell(col);
            CellStyle titleStyle = StyleUtils.getTitleStyle(workbook);
            cell.setCellStyle(titleStyle);
            //设置宽度
            autoWidth(sheet, attr.name(), col);
            // 设置列中写入内容为String类型
            cell.setCellType(CellType.STRING);
            // 写入列名
            cell.setCellValue(attr.name());
            StyleUtils.getComment(workbook, sheet, row, cell, cell.getStringCellValue());
        }
        /* *************创建内容列*************** */
        if (list == null || CollectionUtils.isEmpty(list)) {
            return;
        }
        font = workbook.createFont();
        cellStyle = workbook.createCellStyle();
        int startNo = row.getCell(0).getCellComment() == null ? row.getRowNum() + 1 : 1;
        // 写入各条记录,每条记录对应excel表中的一行
        for (T t : list) {
            row = sheet.createRow(startNo);
            startNo++;
            // 得到导出对象.
            for (int j = 0; j < fields.size(); j++) {
                // 获得field
                Field field = fields.get(j);
                // 设置实体类私有属性可访问
                field.setAccessible(true);
                Excel attr = field.getAnnotation(Excel.class);
                int col = j;
                // 根据指定的顺序获得列号
                if (StringUtils.hasText(attr.column())) {
                    col = getExcelCol(attr.column());
                }
                // 根据HqExcel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
                if (attr.isExport()) {
                    // 创建cell
                    cell = row.createCell(col);
                    if (attr.isMark()) {
                        newFont.setColor(HSSFFont.COLOR_RED);
                        newCellStyle.setFont(newFont);
                        cell.setCellStyle(newCellStyle);
                    } else {
                        font.setColor(HSSFFont.COLOR_NORMAL);
                        cellStyle.setFont(font);
                        cell.setCellStyle(cellStyle);
                    }
                    // 如果数据存在就填入,不存在填入空格
                    if (field.get(t) != null) {
                        setCellValue(cell, field.get(t));
                    }
                    //替换需要配置的数据列字典值
                    if (attr.replace().length > 0 && field.get(t) != null) {
                        for (String data : attr.replace()) {
                            String prefix = data.substring(0, data.lastIndexOf("_"));
                            String suffix = data.substring(data.lastIndexOf("_") + 1);
                            if (prefix.equals(field.get(t).toString())) {
                                cell.setCellValue(suffix);
                                break;
                            }
                        }
                    }
                    //设置时间格式
                    if (field.get(t) != null && field.get(t).getClass() != null && Date.class.equals(field.get(t).getClass())) {
                        //设置自定义注解的时间格式
                        if (attr.dateFormat() != null) {
                            dateStyle.setDataFormat(dataFormat.getFormat(attr.dateFormat()));
                        }
                        cell.setCellStyle(dateStyle);
                    }
                }
            }
        }
        /* *************创建合计列  - 暂不需要*************** */
//        SXSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
//        for (int i = 0; i < fields.size(); i++) {
//            Field field = fields.get(i);
//            HqExcel attr = field.getAnnotation(HqExcel.class);
//            if (attr.isSum()) {
//                int col = i;
//                // 根据指定的顺序获得列号
//                if (StringUtils.hasText(attr.column())) {
//                    col = getExcelCol(attr.column());
//                }
//                BigDecimal totalNumber = BigDecimal.ZERO;
//
//                for (int j = 1, len = (sheet.getLastRowNum() - 1); j < len; j++) {
//                    SXSSFRow hssfRow = sheet.getRow(j);
//                    if (hssfRow != null) {
//                        SXSSFCell hssfCell = hssfRow.getCell(col);
////                                if (hssfCell != null && hssfCell.getCellType() == HSSFCell.CELL_TYPE_STRING
////                                        && ValidateUtil.isFloat(hssfCell.getStringCellValue())) {
////                                    totalNumber = BigDecimalUtils.getValue(totalNumber,
////                                            BigDecimal.valueOf(Double.valueOf(hssfCell.getStringCellValue())), CalculateType.Add);
////                                }
//                        totalNumber = totalNumber.add(new BigDecimal(hssfCell.getStringCellValue()));
//                    }
//                }
//                SXSSFCell sumCell = lastRow.createCell(col);
//                sumCell.setCellValue(new HSSFRichTextString("合计：" + totalNumber));
//            }
//        }
    }

    /**
     * 设置合并列的样式和字体颜色
     *
     * @param sheetName 名称
     * @param fields    成员
     * @param workbook  工作薄
     * @param sheet     sheet
     * @param rowHeader 行
     */
    private static void getHeaderToClass(String sheetName, List<Field> fields, SXSSFWorkbook workbook, SXSSFSheet sheet, SXSSFRow rowHeader) {
        rowHeader.setHeight((short) (12 * 80));
        SXSSFCell cellHeader = rowHeader.createCell(0);
        CellStyle headerStyle = StyleUtils.getHeaderStyle(workbook);
        cellHeader.setCellStyle(headerStyle);
        cellHeader.setCellValue(sheetName);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fields.size() - 1));
        //设置批注
        Comment comment = StyleUtils.getComment(workbook, sheet, rowHeader, cellHeader, sheetName);
        // 将注释分配给单元格
        cellHeader.setCellComment(comment);
    }

    private static void autoWidth(SXSSFSheet sheet, String value, int col) {
        //手动调整列宽，解决中文不能自适应问题
        try {
            sheet.setColumnWidth(col, value.getBytes().length * 2 * 256);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sheet.setColumnWidth(col, value.getBytes().length * 256);
        }
    }


    /**
     * 设置单元格的值
     *
     * @param cell    单元格
     * @param cellVal 单元格的值
     */
    private static void setCellValue(Cell cell, Object cellVal) {
        if (cellVal == null) {
            cell.setCellValue("");
        } else if (String.class.equals(cellVal.getClass())) {
            cell.setCellValue((String) cellVal);
        } else if (Integer.class.equals(cellVal.getClass())) {
            cell.setCellValue(Integer.parseInt(cellVal.toString()));
        } else if (Long.class.equals(cellVal.getClass())) {
            cell.setCellValue(Long.parseLong(cellVal.toString()));
        } else if (Double.class.equals(cellVal.getClass())) {
            cell.setCellValue(Double.parseDouble(cellVal.toString()));
        } else if (Float.class.equals(cellVal.getClass())) {
            cell.setCellValue(Float.parseFloat(cellVal.toString()));
        } else if (BigDecimal.class.equals(cellVal.getClass())) {
            cell.setCellValue(new BigDecimal(cellVal.toString()).doubleValue());
        } else if (Date.class.equals(cellVal.getClass())) {
            cell.setCellValue((Date) cellVal);
        } else {
            cell.setCellValue(cellVal.toString());
        }
    }

    /**
     * 将EXCEL中A,B,C,D,E列映射成0,1,2,3
     *
     * @param col 列名
     */
    private static int getExcelCol(String col) {
        col = col.toUpperCase();
        // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
        int count = -1;
        char[] cs = col.toCharArray();
        for (int i = 0; i < cs.length; i++) {
            count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
        }
        return count;
    }

    /**
     * 设置单元格上提示
     *
     * @param sheet         要设置的sheet.
     * @param promptTitle   标题
     * @param promptContent 内容
     * @param firstRow      开始行
     * @param endRow        结束行
     * @param firstCol      开始列
     * @param endCol        结束列
     * @return 设置好的sheet.
     */
    private static SXSSFSheet setHSSFPrompt(SXSSFSheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                                            int firstCol, int endCol) {
        // 构造constraint对象
        DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation dataValidationView = new HSSFDataValidation(regions, constraint);
        dataValidationView.createPromptBox(promptTitle, promptContent);
        sheet.addValidationData(dataValidationView);
        return sheet;
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet    要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow   结束行
     * @param firstCol 开始列
     * @param endCol   结束列
     * @return 设置好的sheet.
     */
    private static SXSSFSheet setHSSFValidation(SXSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation datavalidationlist = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(datavalidationlist);
        return sheet;
    }

    /**
     * 导出文件流
     *
     * @param fileName 文件名
     * @param workbook 工作铺
     * @param response 输出流
     */
    private static void export(String fileName, SXSSFWorkbook workbook, HttpServletResponse response) {
        try (OutputStream ouputStream = response.getOutputStream()) {
            // 设置输出的格式
            String name = URLEncoder.encode(fileName + ".xlsx", "UTF-8");
            //自定义的header
            response.setHeader("requestType", "file");
            // 自定义的header
            response.setHeader("requestFileName", name);
            //设置这个header 可见
            response.setHeader("Access-Control-Expose-Headers", "requestType,requestFileName");

            //设置响应类型为html，编码为utf-8，处理相应页面文本显示的乱码
            response.setContentType("application/octet-stream;charset=UTF-8");
            //设置文件头：最后一个参数是设置下载文件名
            response.setHeader("Content-disposition", "attachment;fileName=" + name);
            workbook.write(ouputStream);
            ouputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * workbook转inputStream
     * @param workbook workbook
     * @return  inputStream
     */
    public static InputStream  workbookConvertorStream(SXSSFWorkbook workbook){
        InputStream in = null;
        try{
            //创建临时缓存区
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            workbook.write(out);
            byte[] bytes = out.toByteArray();
            in = new ByteArrayInputStream(bytes);
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return in;
    }


}
